package jdbc;

import bean.User;
import utils.JDBCUtils;
import utils.JDBCUtilsLast;
import utils.JDBCUtilsOld;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Date:2022/2/28
 * Author:ybc
 * Description:
 * JDBC从MySQL中查询数据：
 * 查询一条数据-->实体类对象
 * 查询多条数据-->
 */
public class SelectTest {

    @Test
    public void testSelectSingleData(){
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root",
                    "123456");
            statement = connection.createStatement();
            rs = statement.executeQuery("select count(*) from t_user");
            int count = 0;
            if(rs.next()) {
                count = rs.getInt(1);
            }
            System.out.println("总记录数："+count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsOld.close(rs, statement, connection);
        }
    }

    @Test
    public void testSelectMoreByDruid(){
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            rs = statement.executeQuery("select * from t_user");
            List<User> list = new ArrayList<>();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setAge(rs.getInt(4));
                user.setSex(rs.getString(5));
                user.setEmail(rs.getString(6));
                list.add(user);
            }
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsOld.close(rs, statement, connection);
        }
    }

    @Test
    public void testSelectMore(){
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root",
                    "123456");
            statement = connection.createStatement();
            rs = statement.executeQuery("select * from t_user");
            List<User> list = new ArrayList<>();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setAge(rs.getInt(4));
                user.setSex(rs.getString(5));
                user.setEmail(rs.getString(6));
                list.add(user);
            }
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsOld.close(rs, statement, connection);
        }
    }

    @Test
    public void testSelectOne(){
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root",
                    "123456");
            statement = connection.createStatement();
            rs = statement.executeQuery("select * from t_user where id = 1");
            User user = null;
            while (rs.next()){
                user = new User();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setAge(rs.getInt(4));
                user.setSex(rs.getString(5));
                user.setEmail(rs.getString(6));
            }
            System.out.println(user);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsOld.close(rs, statement, connection);
        }
    }

}
